require('dotenv').config();
const mysql = require('mysql2/promise');
const bcrypt = require('bcryptjs');

async function main() {
  const {
    DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT
  } = process.env;
  const port = DB_PORT ? parseInt(DB_PORT) : 3306;

  const conn = await mysql.createConnection({ host: DB_HOST, user: DB_USER, password: DB_PASS, port, multipleStatements: true });
  await conn.query(`CREATE DATABASE IF NOT EXISTS \`${DB_NAME}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`);
  await conn.end();

  const pool = mysql.createPool({ host: DB_HOST, user: DB_USER, password: DB_PASS, database: DB_NAME, port, waitForConnections: true, connectionLimit: 10 });

  // Create tables
  await pool.query(`
    CREATE TABLE IF NOT EXISTS roles (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL UNIQUE
    );
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(100) NOT NULL UNIQUE,
      password VARCHAR(255) NOT NULL,
      role_id INT,
      FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL
    );
    CREATE TABLE IF NOT EXISTS menus (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(200) NOT NULL,
      path VARCHAR(255),
      parent_id INT DEFAULT NULL,
      sort_order INT DEFAULT 0,
      permission_key VARCHAR(200)
    );
    CREATE TABLE IF NOT EXISTS role_menus (
      role_id INT NOT NULL,
      menu_id INT NOT NULL,
      PRIMARY KEY (role_id, menu_id),
      FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
      FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE
    );
  `);

  // Seed roles
  const [existingRoles] = await pool.query('SELECT id, name FROM roles');
  if (existingRoles.length === 0) {
    await pool.query('INSERT INTO roles (name) VALUES (?)', ['admin']);
    await pool.query('INSERT INTO roles (name) VALUES (?)', ['user']);
  }
  const [roles] = await pool.query('SELECT id, name FROM roles');
  const roleMap = {};
  roles.forEach(r => roleMap[r.name] = r.id);

  // Seed admin user
  const adminPassword = 'admin123';
  const hashed = await bcrypt.hash(adminPassword, 10);
  const [urows] = await pool.query('SELECT id FROM users WHERE username = ?', ['admin']);
  if (urows.length === 0) {
    await pool.query('INSERT INTO users (username, password, role_id) VALUES (?, ?, ?)', ['admin', hashed, roleMap['admin']]);
    console.log('Created admin user with username: admin and password: admin123');
  } else {
    console.log('Admin user already exists, skipping creation');
  }

  // Seed menus
  const [mrows] = await pool.query('SELECT id FROM menus LIMIT 1');
  if (mrows.length === 0) {
    const menus = [
      { name: 'Dashboard', path: '/dashboard', parent_id: null, sort_order: 1, permission_key: 'dashboard:view' },
      { name: 'Users', path: '/users', parent_id: null, sort_order: 2, permission_key: 'users:view' },
      { name: 'User List', path: '/users/list', parent_id: 2, sort_order: 1, permission_key: 'users:list' },
      { name: 'Roles', path: '/roles', parent_id: null, sort_order: 3, permission_key: 'roles:view' },
      { name: 'Settings', path: '/settings', parent_id: null, sort_order: 4, permission_key: 'settings:view' }
    ];
    for (const m of menus) {
      await pool.query('INSERT INTO menus (name, path, parent_id, sort_order, permission_key) VALUES (?, ?, ?, ?, ?)', [m.name, m.path, m.parent_id, m.sort_order, m.permission_key]);
    }
    console.log('Seeded menus');
  } else {
    console.log('Menus exist, skipping menu seeding');
  }

  // Assign menus to roles
  const [allMenus] = await pool.query('SELECT id, name FROM menus');
  const menuMap = {};
  allMenus.forEach(m => menuMap[m.name] = m.id);

  // grant all menus to admin
  for (const menuId of Object.values(menuMap)) {
    await pool.query('INSERT IGNORE INTO role_menus (role_id, menu_id) VALUES (?, ?)', [roleMap['admin'], menuId]);
  }
  // grant dashboard only to user
  if (menuMap['Dashboard'] && roleMap['user']) {
    await pool.query('INSERT IGNORE INTO role_menus (role_id, menu_id) VALUES (?, ?)', [roleMap['user'], menuMap['Dashboard']]);
  }

  console.log('Seeding complete');
  await pool.end();
}

main().catch(err => {
  console.error('Seed error', err);
  process.exit(1);
});